package com.derbysoft.dswitch.adapter.ihg.common.repository.impl;

import com.derbysoft.dswitch.adapter.ihg.common.domain.Reservation;
import com.derbysoft.dswitch.adapter.ihg.common.domain.ReservationItem;
import com.derbysoft.dswitch.adapter.ihg.common.domain.Status;
import com.derbysoft.dswitch.adapter.ihg.common.repository.ReservationItemRepository;
import com.derbysoft.dswitch.adapter.ihg.common.repository.ReservationRepository;
import com.derbysoft.dswitch.adapter.ihg.common.utils.CollectionUtil;
import com.derbysoft.dswitch.adapter.ihg.common.utils.DateTimeUtils;
import com.derbysoft.dswitch.adapter.ihg.web.action.admin.reservation.DMXQueryReservationCondition;
import com.derbysoft.dswitch.adapter.ihg.web.action.admin.reservation.DMXReservationDetail;
import com.derbysoft.dswitch.adapter.ihg.web.action.admin.reservation.ReservationCondition;
import com.derbysoft.dswitch.adapter.ihg.web.action.admin.reservation.ReservationInfo;
import com.derbysoft.dswitch.adapter.ihg.web.action.support.Paginater;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository("reservationRepository")
public class ReservationRepositoryImpl extends CommonService<Reservation> implements ReservationRepository {

    private static final String ERSP = "ersp";
    private static final String CREATE_TIME = "createTime";
    private static final String OPERATION_TYPE = "status";

    @Autowired
    private ReservationItemRepository reservationItemRepository;

    private final Log logger = LogFactory.getLog(ReservationRepositoryImpl.class);

    @Override
    public void paginater(DMXQueryReservationCondition condition, Paginater paginater) {
        if (queryReservationItem(condition)) {
            if (StringUtils.isNotBlank(condition.getErspNo())) {
                Reservation reservation = load("ersp", condition.getErspNo());
                if (reservation == null) {
                    return;
                }
                createReservationDetail(reservationItemRepository.loadByErsp(condition.getErspNo()), reservation, paginater);
            }
            if (StringUtils.isNotBlank(condition.getConfirmNo())) {
                ReservationItem reservationItem = reservationItemRepository.loadByConfirm(condition.getConfirmNo());
                if (reservationItem == null) {
                    return;
                }
                createReservationDetail(reservationItem, load("ersp", condition.getErspNo()), paginater);
            }
        } else {
            createReservationDetails(condition, paginater);
        }
    }

    private void createReservationDetails(DMXQueryReservationCondition condition, Paginater paginater) {
        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Reservation.class).addOrder(Order.desc("lastUpdateTime"));
        if (StringUtils.isNotBlank(condition.getStartDate()) || StringUtils.isNotBlank(condition.getEndDate())) {
            if (StringUtils.isNotBlank(condition.getStartDate())) {
                gt(detachedCriteria, CREATE_TIME, DateTimeUtils.parse(condition.getStartDate()));
            }
            if (StringUtils.isNotBlank(condition.getEndDate())) {
                lt(detachedCriteria, CREATE_TIME, DateTimeUtils.addDays(DateTimeUtils.parse(condition.getEndDate()), 1));
            }
        }
        if (StringUtils.isNotBlank(condition.getHotelCode())) {
            ilike(detachedCriteria, "hotelPassport", condition.getHotelCode());
        }
        if (condition.getOperationType() != null) {
            eq(detachedCriteria, OPERATION_TYPE, condition.getOperationType());
        }
        List<DMXReservationDetail> dmxReservationDetails = new ArrayList<DMXReservationDetail>();
        for (Object obj : paginater(detachedCriteria, paginater).getObjects()) {
            DMXReservationDetail dmxReservationDetail = new DMXReservationDetail();
            Reservation reservation = (Reservation) obj;
            dmxReservationDetail.setReservation(reservation);
            dmxReservationDetail.setReservationItem(reservationItemRepository.loadByErsp(reservation.getErsp()));
            dmxReservationDetails.add(dmxReservationDetail);
        }
        paginater.getObjects().clear();
        paginater.getObjects().addAll(dmxReservationDetails);
    }

    private void createReservationDetail(ReservationItem reservationItem, Reservation reservation, Paginater paginater) {
        DMXReservationDetail dmxReservationDetail = new DMXReservationDetail();
        dmxReservationDetail.setReservation(reservation);
        dmxReservationDetail.setReservationItem(reservationItem);
        paginater.getObjects().clear();
        paginater.setTotalCount(1);
        paginater.getObjects().add(dmxReservationDetail);
    }

    private boolean queryReservationItem(DMXQueryReservationCondition condition) {
        return StringUtils.isNotBlank(condition.getErspNo())
                || StringUtils.isNotBlank(condition.getConfirmNo());
    }

    private void queryReservation(Paginater paginater, DetachedCriteria detachedCriteria) {
        List<ReservationInfo> reservationInfo =
                reservationItemRepository.loadAll(paginater(detachedCriteria, paginater).getObjects());
        paginater.getObjects().clear();
        paginater.getObjects().addAll(reservationInfo);
    }

    @Override
    @SuppressWarnings("unchecked")
    public Paginater paginater(ReservationCondition condition, Paginater paginater) {
        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Reservation.class).addOrder(Order.desc("createTime"));
        if (condition == null) {
            return createReservations(paginater(detachedCriteria, paginater));
        }
        if (condition.getStatus() != null) {
            detachedCriteria.add(Restrictions.eq("status", condition.getStatus()));
        }
        if (StringUtils.isNotBlank(condition.getConfirmNumber())) {
            return loadByConfirmNumber(condition, paginater);
        }

        if (StringUtils.isNotBlank(condition.getErsp())) {
            return loadByErsp(condition, paginater);
        }

        if (StringUtils.isNotBlank(condition.getHotelPassport())) {
            detachedCriteria.add(Restrictions.eq("hotelPassport", condition.getHotelPassport()));
            queryReservation(paginater, detachedCriteria);
            return paginater;
        }
        return createReservations(paginater(detachedCriteria, paginater));
    }

    private Paginater createReservations(Paginater paginater) {
        List<Reservation> reservations = new ArrayList<Reservation>();
        for (Object object : paginater.getObjects()) {
            reservations.add((Reservation) object);
        }
        paginater.getObjects().clear();
        paginater.setObjects(reservationItemRepository.loadAll(reservations));
        return paginater;
    }

    @SuppressWarnings("unchecked")
    private Paginater loadByErsp(ReservationCondition condition, Paginater paginater) {
        Reservation reservation = load(ERSP, condition.getErsp());
        ReservationItem reservationItem = reservationItemRepository.loadByErsp(condition.getErsp());
        if (reservation != null) {
            paginater.getObjects().clear();
            paginater.getObjects().add(ReservationInfo.of(reservation, reservationItem));
            return paginater;
        }
        return paginater;
    }

    @SuppressWarnings("unchecked")
    private Paginater loadByConfirmNumber(ReservationCondition condition, Paginater paginater) {
        ReservationItem reservationItem = reservationItemRepository.loadByConfirm(condition.getConfirmNumber());
        if (reservationItem != null) {
            Reservation reservation = load("ersp", reservationItem.getErsp());
            if (reservation != null) {
                paginater.getObjects().clear();
                paginater.getObjects().add(ReservationInfo.of(reservation, reservationItem));
                return paginater;
            }
        }
        return paginater;
    }

    @Override
    @Transactional(readOnly = true)
    public boolean existReservation(final String ersp) {
        String reservationSQL = String.format("SELECT COUNT(*) FROM reservation a WHERE a.ersp = '%s';", ersp);
        return jdbcTemplate.queryForLong(reservationSQL) == 1L;
    }

    @Override
    @Transactional(readOnly = true)
    public String findConfirmNumber(String ersp) {
        String sql = String.format("SELECT confirm_number FROM reservation_item WHERE ersp = '%s';", ersp);
        List<String> results = jdbcTemplate.query(sql, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs == null ? null : rs.getString(1);
            }
        });
        return CollectionUtil.isNotEmpty(results) ? results.get(0) : null;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void create(final String ersp, final String hotelPassport, final String status) {
        final Date date = new Date();
        String reservation = "INSERT INTO reservation(ersp,create_time,hotel_passport,last_update_time,status,modify_times) VALUES(?,?,?,?,?,?);";
        jdbcTemplate.update(reservation, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                Timestamp timestamp = new Timestamp(date.getTime());
                ps.setString(1, ersp);
                ps.setTimestamp(2, timestamp);
                ps.setString(3, hotelPassport);
                ps.setTimestamp(4, timestamp);
                ps.setString(5, status);
                ps.setInt(6, 0);
            }
        });
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void addReservationItem(final ReservationItem reservationItem) {
        String reservationItemSQL = "INSERT INTO reservation_item(ersp,confirm_number,confirm_time,surename,given_name," +
                "start,duration,rate_plan_code,room_type_code,adults,children,number_of_unit) VALUES(?,?,?,?,?,?,?,?,?,?,?,?);";
        jdbcTemplate.update(reservationItemSQL, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, reservationItem.getErsp());
                ps.setString(2, reservationItem.getConfirmNumber());
                ps.setTimestamp(3, new Timestamp(reservationItem.getConfirmTime().getTime()));
                ps.setString(4, reservationItem.getSurename());
                ps.setString(5, reservationItem.getGivenName());
                ps.setString(6, reservationItem.getStart());
                ps.setInt(7, reservationItem.getDuration());
                ps.setString(8, reservationItem.getRatePlanCode());
                ps.setString(9, reservationItem.getRoomTypeCode());
                ps.setInt(10, reservationItem.getAdults());
                ps.setInt(11, reservationItem.getChildren());
                ps.setInt(12, reservationItem.getNumberOfUnit());
            }
        });
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateStatus(final String ersp, final Status status) {
        String updateStatus;
        if (status == Status.MODIFIED || status == Status.MODIFY_FAILED) {
            updateStatus = "UPDATE reservation SET STATUS = ?,last_update_time = ?,modify_times = modify_times + 1 WHERE ersp= ?;";
        } else {
            updateStatus = "UPDATE reservation SET STATUS = ?,last_update_time = ? WHERE ersp= ?;";
        }
        jdbcTemplate.update(updateStatus, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, status.name());
                ps.setTimestamp(2, new Timestamp(new Date().getTime()));
                ps.setString(3, ersp);
            }
        });
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void cancelReservationItem(final String ersp, final String cancelNumber) {
        final String updateStatus = "UPDATE reservation SET STATUS = ?,last_update_time = ? WHERE ersp=?;";
        final String updateResItem = "UPDATE reservation_item SET cancel_number = ?,cancel_time = ? WHERE  ersp = ?;";

        final Timestamp timestamp = new Timestamp(new Date().getTime());

        String reservationSQL = String.format("SELECT COUNT(*) FROM reservation a WHERE a.ersp = '%s';", ersp);
        long reservationCount = jdbcTemplate.queryForLong(reservationSQL);
        if (reservationCount == 1L) {
            jdbcTemplate.update(updateStatus, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, Status.CANCELLED.name());
                    ps.setTimestamp(2, timestamp);
                    ps.setString(3, ersp);
                }
            });
        } else {
            logger.warn("load reservation failed !! ersp: " + ersp);
        }

        String reservationItemSQL = String.format("SELECT COUNT(*) FROM reservation_item a WHERE a.ersp = '%s';", ersp);
        long reservationItemCount = jdbcTemplate.queryForLong(reservationItemSQL);
        if (reservationItemCount == 1) {
            jdbcTemplate.update(updateResItem, new PreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setString(1, cancelNumber);
                    ps.setTimestamp(2, timestamp);
                    ps.setString(3, ersp);
                }
            });
        } else {
            logger.warn("load reservation_item failed !! ersp: " + ersp);
        }
    }
}